from flask import Blueprint, render_template, request, flash
import pymysql
from pymysql.cursors import DictCursor

# 创建蓝图
search_bp = Blueprint('search', __name__, url_prefix='/search')

# 数据库配置
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'database': 'Ab_data',
    'charset': 'utf8mb4',
    'cursorclass': DictCursor
}


def get_db_connection():
    """创建数据库连接"""
    return pymysql.connect(**DB_CONFIG)


def get_abalone_by_id(abalone_id):
    """根据ID查询鲍鱼信息"""
    query = """
    SELECT id, sex, 
           length, diameter, height,
           whole_weight, shucked_weight, 
           viscera_weight, shell_weight,
           rings, 
           predicted_rings_LR, predicted_rings_DT_R, predicted_rings_RF_R,
           prediction_time_LR, prediction_time_DT_R, prediction_time_RF_R
    FROM new_abalone
    WHERE id = %s
    """

    connection = get_db_connection()
    try:
        with connection.cursor() as cursor:
            cursor.execute(query, (abalone_id,))
            result = cursor.fetchone()

            # 转换性别显示
            if result:
                result['sex_display'] = {
                    'M': '雄性',
                    'F': '雌性',
                    'I': '幼体'
                }.get(result['sex'], '未知')

                # 转换数值格式
                result['length'] = round(result['length'], 4)
                result['diameter'] = round(result['diameter'], 4)
                result['height'] = round(result['height'], 4)
                result['whole_weight'] = round(result['whole_weight'], 4)
                result['shucked_weight'] = round(result['shucked_weight'], 4)
                result['viscera_weight'] = round(result['viscera_weight'], 4)
                result['shell_weight'] = round(result['shell_weight'], 4)

                # 处理预测年龄
                result['predictions'] = {
                    'LR': result['predicted_rings_LR'],
                    'DT-R': result['predicted_rings_DT_R'],
                    'RF-R': result['predicted_rings_RF_R']
                }

                # 处理预测时间
                result['prediction_times'] = {
                    'LR': result['prediction_time_LR'],
                    'DT-R': result['prediction_time_DT_R'],
                    'RF-R': result['prediction_time_RF_R']
                }

            return result
    finally:
        connection.close()


@search_bp.route('/', methods=['GET', 'POST'])
def search():
    """查询功能主路由"""
    abalone_info = None
    abalone_id = None

    if request.method == 'POST':
        abalone_id = request.form.get('abalone_id')

        if not abalone_id:
            flash('请输入鲍鱼编号', 'error')
        else:
            try:
                abalone_id = int(abalone_id)
                abalone_info = get_abalone_by_id(abalone_id)
                if not abalone_info:
                    flash(f'未找到编号为 {abalone_id} 的鲍鱼记录', 'error')
            except ValueError:
                flash('请输入有效的数字编号', 'error')

    return render_template('search.html',
                           abalone_info=abalone_info,
                           abalone_id=abalone_id)